"""
数据分析路由 - 核心分析API
"""
from flask import request, jsonify
from flask_jwt_extended import jwt_required, get_jwt_identity
from app.blueprints.analytics import analytics_bp
from app.models.course import Course
from app.models.student_course import StudentCourse
from app.models.user import User, StudentProfile
from app.extensions import db
import pandas as pd
import numpy as np

# 导入活跃度分析路由
from app.blueprints.analytics import activity_routes


@analytics_bp.route('/courses/<int:course_id>/grade_distribution', methods=['GET'])
@jwt_required()
def get_grade_distribution(course_id):
    """获取课程成绩分布"""
    try:
        course = Course.query.get(course_id)
        if not course:
            return jsonify({
                'success': False,
                'error': 'Course not found'
            }), 404
        
        # 获取所有成绩（不需要修改，这里是按course_id查询）
        student_courses = StudentCourse.query.filter_by(
            course_id=course_id,
            status='completed'
        ).all()
        
        if not student_courses:
            return jsonify({
                'success': False,
                'error': 'No grade data available for this course'
            }), 404
        
        # 使用Pandas进行分箱统计
        scores = [float(sc.score) for sc in student_courses if sc.score is not None]
        
        if not scores:
            return jsonify({
                'success': False,
                'error': 'No valid scores found'
            }), 404
        
        bins = [0, 60, 70, 80, 90, 101]
        labels = ['0-59 (不及格)', '60-69 (及格)', '70-79 (中等)', '80-89 (良好)', '90-100 (优秀)']
        
        df = pd.DataFrame({'score': scores})
        df['grade_level'] = pd.cut(df['score'], bins=bins, labels=labels, right=False)
        
        distribution = df['grade_level'].value_counts().reindex(labels, fill_value=0).to_dict()
        
        # 计算统计数据
        stats = {
            'mean': float(np.mean(scores)),
            'median': float(np.median(scores)),
            'std': float(np.std(scores)),
            'min': float(np.min(scores)),
            'max': float(np.max(scores)),
            'total_students': len(scores),
            'fail_count': int(df[df['score'] < 60].shape[0]),
            'fail_rate': float(df[df['score'] < 60].shape[0] / len(scores) * 100)
        }
        
        return jsonify({
            'success': True,
            'data': {
                'course_name': course.name,
                'course_code': course.code,
                'distribution': distribution,
                'statistics': stats
            }
        }), 200
        
    except Exception as e:
        return jsonify({
            'success': False,
            'error': str(e)
        }), 500


@analytics_bp.route('/students/<int:student_id>/gpa_trend', methods=['GET'])
@jwt_required()
def get_gpa_trend(student_id):
    """获取学生GPA趋势"""
    try:
        user = User.query.get(student_id)
        if not user or user.role != 'student':
            return jsonify({
                'success': False,
                'error': 'Student not found'
            }), 404
        
        # 获取学生所有已完成的课程
        student_courses = StudentCourse.query.filter_by(
            student_id=student_id,
            status='completed'
        ).order_by(StudentCourse.enrollment_date).all()
        
        if not student_courses:
            return jsonify({
                'success': False,
                'error': 'No course data available'
            }), 404
        
        # 计算GPA趋势（按学期）- 使用 student_courses 表中的学期信息
        df = pd.DataFrame([{
            'academic_year': sc.academic_year,
            'semester_term': sc.semester_term,
            'score': float(sc.score) if sc.score else 0,
            'credits': float(sc.course.credits) if sc.course.credits else 0,
            'enrollment_date': sc.enrollment_date
        } for sc in student_courses if sc.score and sc.course and sc.academic_year and sc.semester_term])
        
        if df.empty:
            return jsonify({
                'success': False,
                'error': 'No valid data for GPA calculation'
            }), 404
        
        # 创建学期标识（用于排序和显示）
        df['semester_key'] = df['academic_year'] + '-' + df['semester_term']
        
        # 按学期分组计算GPA
        def score_to_gpa(score):
            if score >= 90:
                return 4.0
            elif score >= 80:
                return 3.0
            elif score >= 70:
                return 2.0
            elif score >= 60:
                return 1.0
            else:
                return 0.0
        
        df['gpa_point'] = df['score'].apply(score_to_gpa)
        df['weighted_gpa'] = df['gpa_point'] * df['credits']
        
        # 按学期分组计算加权GPA
        semester_gpa = df.groupby('semester_key').apply(
            lambda x: x['weighted_gpa'].sum() / x['credits'].sum() if x['credits'].sum() > 0 else 0
        ).to_dict()
        
        # 计算累积GPA
        total_weighted = df['weighted_gpa'].sum()
        total_credits = df['credits'].sum()
        cumulative_gpa = total_weighted / total_credits if total_credits > 0 else 0
        
        return jsonify({
            'success': True,
            'data': {
                'student_name': f"{user.last_name}{user.first_name}",
                'semester_gpa': semester_gpa,
                'cumulative_gpa': float(cumulative_gpa),
                'total_credits': float(total_credits),
                'courses_completed': len(student_courses)
            }
        }), 200
        
    except Exception as e:
        return jsonify({
            'success': False,
            'error': str(e)
        }), 500


@analytics_bp.route('/courses/<int:course_id>/statistics', methods=['GET'])
@jwt_required()
def get_course_statistics(course_id):
    """获取课程统计信息"""
    try:
        course = Course.query.get(course_id)
        if not course:
            return jsonify({
                'success': False,
                'error': 'Course not found'
            }), 404
        
        # 获取课程所有选课记录
        student_courses = StudentCourse.query.filter_by(course_id=course_id).all()
        
        if not student_courses:
            return jsonify({
                'success': False,
                'error': 'No enrollment data available'
            }), 404
        
        # 统计数据
        total_enrolled = len(student_courses)
        completed = len([sc for sc in student_courses if sc.status == 'completed'])
        dropped = len([sc for sc in student_courses if sc.status == 'dropped'])
        
        scores = [float(sc.score) for sc in student_courses if sc.score is not None]
        
        statistics = {
            'total_enrolled': total_enrolled,
            'completed': completed,
            'dropped': dropped,
            'completion_rate': float(completed / total_enrolled * 100) if total_enrolled > 0 else 0,
            'drop_rate': float(dropped / total_enrolled * 100) if total_enrolled > 0 else 0
        }
        
        if scores:
            statistics.update({
                'average_score': float(np.mean(scores)),
                'highest_score': float(np.max(scores)),
                'lowest_score': float(np.min(scores)),
                'pass_rate': float(len([s for s in scores if s >= 60]) / len(scores) * 100)
            })
        
        # 出勤率统计
        attendance_rates = [float(sc.attendance_rate) for sc in student_courses if sc.attendance_rate]
        if attendance_rates:
            statistics['average_attendance_rate'] = float(np.mean(attendance_rates))
        
        return jsonify({
            'success': True,
            'data': {
                'course_name': course.name,
                'course_code': course.code,
                'statistics': statistics
            }
        }), 200
        
    except Exception as e:
        return jsonify({
            'success': False,
            'error': str(e)
        }), 500


@analytics_bp.route('/courses/popularity', methods=['GET'])
@jwt_required()
def get_course_popularity():
    """获取课程热度排行"""
    try:
        semester = request.args.get('semester')
        limit = request.args.get('limit', 10, type=int)
        
        # 构建查询
        query = db.session.query(
            Course.id,
            Course.code,
            Course.name,
            Course.semester,
            db.func.count(StudentCourse.id).label('enrollment_count')
        ).join(StudentCourse, Course.id == StudentCourse.course_id)
        
        if semester:
            query = query.filter(Course.semester == semester)
        
        results = query.group_by(Course.id).order_by(
            db.desc('enrollment_count')
        ).limit(limit).all()
        
        courses = [{
            'course_id': r.id,
            'course_code': r.code,
            'course_name': r.name,
            'semester': r.semester,
            'enrollment_count': r.enrollment_count
        } for r in results]
        
        return jsonify({
            'success': True,
            'data': courses
        }), 200
        
    except Exception as e:
        return jsonify({
            'success': False,
            'error': str(e)
        }), 500


@analytics_bp.route('/students/<int:student_id>/performance', methods=['GET'])
@jwt_required()
def get_student_performance(student_id):
    """获取学生学业表现分析"""
    try:
        user = User.query.get(student_id)
        if not user or user.role != 'student':
            return jsonify({
                'success': False,
                'error': 'Student not found'
            }), 404
        
        # 获取学生所有课程
        student_courses = StudentCourse.query.filter_by(student_id=student_id).all()
        
        if not student_courses:
            return jsonify({
                'success': False,
                'error': 'No course data available'
            }), 404
        
        # 构建DataFrame进行分析
        data = []
        for sc in student_courses:
            # 只统计已完成且有成绩的课程
            if sc.course and sc.score is not None and sc.status == 'completed':
                data.append({
                    'course_name': sc.course.name,
                    'department': sc.course.department,
                    'score': float(sc.score),
                    'credits': float(sc.course.credits) if sc.course.credits else 0,
                    'status': sc.status
                })
        
        if not data:
            # 如果没有已完成且有成绩的课程，返回空数据而不是404
            return jsonify({
                'success': True,
                'data': {
                    'student_name': f"{user.last_name}{user.first_name}",
                    'performance': {
                        'total_courses': len(student_courses),
                        'completed_courses': len([sc for sc in student_courses if sc.status == 'completed']),
                        'average_score': 0,
                        'highest_score': 0,
                        'lowest_score': 0,
                        'department_performance': {}
                    }
                }
            }), 200
        
        df = pd.DataFrame(data)
        
        # 按院系分组统计
        dept_performance = df.groupby('department').agg({
            'score': ['mean', 'count']
        }).round(2).to_dict()
        
        # 重新整理数据格式
        department_stats = {}
        if 'score' in dept_performance:
            for dept in df['department'].unique():
                dept_data = df[df['department'] == dept]
                department_stats[dept] = {
                    'average_score': float(dept_data['score'].mean()),
                    'course_count': int(dept_data.shape[0])
                }
        
        # 整体表现
        overall = {
            'total_courses': len(student_courses),
            'completed_courses': len([sc for sc in student_courses if sc.status == 'completed']),
            'average_score': float(df['score'].mean()),
            'highest_score': float(df['score'].max()),
            'lowest_score': float(df['score'].min()),
            'department_performance': department_stats
        }
        
        return jsonify({
            'success': True,
            'data': {
                'student_name': f"{user.last_name}{user.first_name}",
                'performance': overall
            }
        }), 200
        
    except Exception as e:
        return jsonify({
            'success': False,
            'error': str(e)
        }), 500

